﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace DWH.Data
{
    public static class DWHDBHelper
    {
        #region Task

        public static void PushTaskToDB(string taskName)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var a = from task in db.PPTasks where task.TaskName == taskName select task;

                if (a.Count() == 0)
                {
                    PPTask newTask = new PPTask() { TaskName = taskName };
                    db.AddToPPTasks(newTask);

                    db.SaveChanges();
                }
            }
        }

        public static Dictionary<string, string> GetPPTaskResumeData(string taskName)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                Dictionary<string, string> result = null;

                var tasks = from t in db.PPTasks where t.TaskName == taskName select t;

                if (tasks.Count() == 1)
                {
                    PPTask task = tasks.Single<PPTask>();

                    if (task.PPTaskResumeDatas != null && task.PPTaskResumeDatas.Count > 0)
                    {
                        result = new Dictionary<string, string>();
                        foreach (PPTaskResumeData r in task.PPTaskResumeDatas)
                        {
                            result.Add(r.RKey, r.RData);
                        }
                    }
                }

                return result;
            }

        }

        public static void UpdatePPTask(string taskName, Dictionary<string, string> extendData)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var tasks = from t in db.PPTasks where t.TaskName == taskName select t;

                if (tasks.Count() == 1)
                {
                    PPTask task = tasks.Single<PPTask>();
                    task.LastUpdateTime = DateTime.Now;

                    foreach (KeyValuePair<string, string> data in extendData)
                    {
                        var q = task.PPTaskResumeDatas.Where(c => c.RKey == data.Key);

                        if (q.Count() == 0)
                        {
                            task.PPTaskResumeDatas.Add(
                                new PPTaskResumeData() { PPTask = taskName, RKey = data.Key, RData = data.Value });
                        }
                        else
                        {
                            PPTaskResumeData prData = q.Single();
                            if (prData.RData != data.Value)
                            {
                                prData.RData = data.Value;
                            }
                        }
                    }

                    db.SaveChanges();
                }
            }
        }

        #endregion

        #region Page

        public static void AddPage(string pullTaskName, int pageUrlHashCode, string pageUr, string title, string content, DateTime pullDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from tp in db.Pages where tp.ID == pageUrlHashCode select tp;

                if (q.Count() == 0)
                {
                    Page p = new Page()
                    {
                        PullTask = pullTaskName,
                        ID = pageUrlHashCode,
                        PageUrl = pageUr,
                        PageTitle = title,
                        PageContent = content,
                        PullDate = pullDate
                    };

                    db.AddToPages(p);
                    db.SaveChanges();
                }
            }
        }

        public static void UpdatePage(string pullTaskName, int pageUrlHashCode, string pageUr, string title, string content, DateTime pullDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from tp in db.Pages where tp.ID == pageUrlHashCode select tp;

                if (q.Count() == 1)
                {
                    Page p = q.FirstOrDefault();

                    p.PullTask = pullTaskName;
                    p.ID = pageUrlHashCode;
                    p.PageUrl = pageUr;
                    p.PageTitle = title;
                    p.PageContent = content;
                    p.PullDate = pullDate;

                    db.SaveChanges();
                }
            }
        }

        public static void AddOrUpdatePageList(string pullTaskName, int pageListHashCode, string pageListUrl, DateTime pullDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from tp in db.PageListHistories where tp.ID == pageListHashCode select tp;

                if (q.Count() == 0)
                {
                    PageListHistory p = new PageListHistory()
                    {
                        PullTask = pullTaskName,
                        ID = pageListHashCode,
                        PageListUrl = pageListUrl,
                        PullDate = pullDate
                    };

                    db.AddToPageListHistories(p);
                    db.SaveChanges();
                }
                else
                {
                    PageListHistory last = q.First();
                    last.PullDate = pullDate;

                    db.SaveChanges();
                }
            }
        }

        public static bool IsPageVisited(int pageUrlHashCode)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from p in db.Pages where p.ID == pageUrlHashCode select p;

                if (q.Count() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static bool IsPageListVisited(int pageListUrlHashCode)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from p in db.PageListHistories where p.ID == pageListUrlHashCode select p;

                if (q.Count() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static Dictionary<long, Page> GetPageContentForPush(string pushTaskName, string pullTaskName, int rowCount)
        {
            Dictionary<long, Page> result = null;
            using (DWHDBEntities db = new DWHDBEntities())
            {
                string query = String.Format("select top {0} * from Page where ID not in (select PageId from PushHistory where PushTask = @pushTaskName) and PullTask = @pullTaskName and PageTitle <> 'WebAttachemnt'", rowCount.ToString());
                var param = new DbParameter[] { 
                    new SqlParameter { ParameterName = "pushTaskName", Value = pullTaskName }
                    ,new SqlParameter { ParameterName = "pullTaskName", Value = pullTaskName }
                    //,new SqlParameter { ParameterName = "pageCount", Value = rowCount }
                };
                var pages = db.ExecuteStoreQuery<Page>(query, param);

                result = new Dictionary<long, Page>();

                foreach (Page pp in pages)
                {
                    result.Add(pp.ID, pp);
                }
            }

            return result;
        }

        public static Page GetPageByHashCode(long pageURLHashCode)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from p in db.Pages where p.ID == pageURLHashCode select p;

                return q.FirstOrDefault<Page>();
            }
        }

        #endregion

        #region PageAttachment

        public static void AddUndownloadedPageAttachment(string pageAttachmentUrl, int pageUrlHashCode, string attachmentType, string storedPath, DateTime pullDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                int pageAttachmentId = pageAttachmentUrl.GetHashCode();
                var q = from tp in db.PageAttachments where tp.ID == pageAttachmentId select tp;

                if (q.Count() == 0)
                {
                    PageAttachment p = new PageAttachment()
                    {
                        ID = pageAttachmentUrl.GetHashCode(),
                        PageID = pageUrlHashCode,
                        AttachmentType = attachmentType,
                        PageAttachmentUrl = pageAttachmentUrl,
                        StoredPath = storedPath,
                        Downloaded = (int)PageAttachmentDownloadStatus.Created,
                        PullDate = pullDate
                    };

                    db.AddToPageAttachments(p);
                    db.SaveChanges();
                }
            }
        }

        public static void AddUndownloadedPageAttachments(List<string> pageAttachmentUrls, int pageUrlHashCode, string attachmentType, string storedPath, DateTime pullDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                foreach (string pageAttachmentUrl in pageAttachmentUrls)
                {
                    int pageAttachmentId = pageAttachmentUrl.GetHashCode();
                    var q = from tp in db.PageAttachments where tp.ID == pageAttachmentId select tp;

                    if (q.Count() == 0)
                    {
                        PageAttachment p = new PageAttachment()
                        {
                            ID = pageAttachmentUrl.GetHashCode(),
                            PageID = pageUrlHashCode,
                            AttachmentType = attachmentType,
                            PageAttachmentUrl = pageAttachmentUrl,
                            StoredPath = storedPath,
                            Downloaded = (int)PageAttachmentDownloadStatus.Created,
                            PullDate = pullDate
                        };

                        db.AddToPageAttachments(p);
                    }
                    db.SaveChanges();
                }
            }
        }

        public static List<PageAttachment> GetPageAttachmentByAttachmentType(string attachmentType)
        {
            List<PageAttachment> result = null;
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var attachments = db.GetPageAttachmentByAttachmentTypeAndDownloadStatus(attachmentType, (int)PageAttachmentDownloadStatus.Created);

                //string query = "exec GetPageAttachmentByAttachmentTypeAndDownloadStatus @attachmentType,@downloadStatus";
                //var param = new DbParameter[] { 
                //    new SqlParameter { ParameterName = "attachmentType", Value = attachmentType ,DbType = DbType.String}
                //    ,new SqlParameter { ParameterName = "downloadStatus", Value = (int)PageAttachmentDownloadStatus.Created,DbType = DbType.Int32 }
                //};
                //var attachments = db.ExecuteStoreQuery<PageAttachment>(query);

                result = new List<PageAttachment>();

                foreach (PageAttachment att in attachments)
                {
                    result.Add(att);
                }
            }

            return result;
        }

        public static void UpdatePageAttachmentDownloadStatus(long pAttID,string downloadTask,DateTime pushTime,PageAttachmentDownloadStatus status)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from tp in db.PageAttachments where tp.ID == pAttID select tp;

                if (q.Count() == 1)
                {
                    PageAttachment p = q.FirstOrDefault();

                    p.PullTask = downloadTask;
                    p.PullDate = pushTime;
                    p.Downloaded = (int)status;

                    db.SaveChanges();
                }
            }
        }

        public static bool IsPageAttachmentVisited(int attachmentUrlHashCode)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                var q = from p in db.PageAttachments where p.ID == attachmentUrlHashCode select p;

                if (q.Count() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        #endregion

        #region Push

        public static void AddPushHistory(string pushTaskName, long pageId, DateTime pushDate)
        {
            using (DWHDBEntities db = new DWHDBEntities())
            {
                PushHistory ph = new PushHistory() { PageID = pageId, PushTask = pushTaskName, PushDate = pushDate };

                db.AddToPushHistories(ph);

                db.SaveChanges();
            }
        }

        #endregion
    }
}
